set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
insert overwrite table jms_dm.dm_waybill_collect_intime_rate_dt
select
    main.network_code       as network_code         --网点编码      
   ,main.network_name       as network_name         --网点名称      
   ,dim.city_id             as city_id              --城市编码      
   ,dim.city_desc           as city_desc            --城市名称      
   ,dim.business_principal  as business_principal   --负责人
   ,dim.zone_id             as district_id          --区域(片区)编码
   ,dim.zone_name           as district_desc        --区域(片区)名称
   ,dim.virt_code           as virt_code            --虚拟代理区编码
   ,dim.virt_name           as virt_name            --虚拟代理区名称
   ,sum(main.zhund_cnt  )   as zhund_cnt            --准点交件量     
   ,sum(main.buzhund_cnt)   as buzhund_cnt          --不准点交件票数   
   ,sum(main.all_cnt    )   as all_cnt              --应交件量
   ,nvl(round(sum(main.zhund_cnt)/ sum(main.all_cnt),4),0) as zhund_rate --准点率  
   ,main.dt                   --分区日期      
from (
    select
         receive_network_code       as network_code     --网点编码
        ,receive_network_name       as network_name     --网点名称
        ,hand_over_in_time_num      as zhund_cnt        --准点交件量
        ,hand_over_not_in_time_num  as buzhund_cnt      --不准点交件票数
        ,should_hand_over_num       as all_cnt          --应交件量
        ,date_add(dt,1)             as dt               --分区日期
    from jms_dm.dm_waybill_collect_intime_taking_agg  --交件准点率揽收维度
    where dt = date_sub('{{ execution_date | cst_ds }}',1)
) main 
left join (
    select * 
    from jms_dim.dim_network_whole_massage
) dim on main.network_code = dim.code
group by main.network_code        --网点编码      
        ,main.network_name        --网点名称      
        ,dim.city_id              --城市编码      
        ,dim.city_desc            --城市名称      
        ,dim.business_principal   --负责人
        ,dim.zone_id              --区域(片区)编码
        ,dim.zone_name            --区域(片区)名称
        ,dim.virt_code            --虚拟代理区编码
        ,dim.virt_name            --虚拟代理区名称
        ,main.dt                  --分区日期
distribute by dt ;
